﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using db;
using IceBird;

namespace SmallBonesDB//数据库组写，网络组调用;
{
	public class Group : IceBird.Group
	{
		public Group(){}
		public Group(SqlDataReader reader){
			id = (int)reader[0];
			if(reader[1] != null)
				name = (string)reader[1];
			topicCnt = (int)reader[2];
		}
		override public string ToString(){
			return
				id.ToString() + " " + 
				name + " " + 
				topicCnt.ToString();
		}
	}

	public class Topic : IceBird.Topic
	{
		public Topic(){}
		public Topic(SqlDataReader reader){
			id = (int)reader[0];
			groupId = (int)reader[1];
			title = (string)reader[2];
			time = (DateTime)reader[3];
			userId = (int)reader[4];
			idInGroup = (int)reader[5];
			replyCnt = (int)reader[6];
		}
		override public string ToString()
		{
			return 
				id.ToString() + " " +
				groupId.ToString() + " " + 
				title + " " + 
				time.ToString() + " " + 
				userId.ToString() + " " + 
				idInGroup.ToString() + " " + 
				replyCnt.ToString();
		}
	}

	public class Message : IceBird.Message
	{
		public Message(SqlDataReader reader)
		{
			id = (int)reader[0];
			userId = (int)reader[1];
			time = (DateTime)reader[2];
			topicId = (int)reader[3];
			content = (string)reader[4];
			idInTopic = (int)reader[5];
		}
		override public string ToString()
		{
			return
				id.ToString() + " " + 
				userId.ToString() + " " + 
				time.ToString() + " " + 
				topicId.ToString() + " " +
				content.ToString() + " " +
				idInTopic.ToString();
		}
	}

	public class AssignResult : IceBird.AssignResult
	{
		public AssignResult(){}
		public AssignResult(AssignState assignSt){
			assignState = assignSt;
			userId = 0;
		}
		public AssignResult(int id){
			assignState = AssignState.SUCCEED;
			userId = id;
		}
	}

	public class CreateResult : IceBird.CreateResult
	{
		public CreateResult(){}
		public CreateResult(CreateState createSt){
			createState = createSt;
			id = 0;
		}
		public CreateResult(int Id){
			createState = CreateState.Pass;
			id = Id;
		}
	}

	public class DBToNetwork
	{
		private const string strConnection
			= "integrated security = true;"
			+ "Server = localhost;"
			+ "DataBase = SmallBones";

		private SqlConnection SqlConnect(){
			SqlConnection objConnection = new SqlConnection(strConnection);
			objConnection.Open();
			return objConnection;
		}

		private SqlDataReader SqlOption(SqlConnection connection, string curOP)
		{
			db.Form1.debugText += curOP + "\n";
			return (new SqlCommand(curOP, connection)).ExecuteReader();
		}

		/// <summary>
		/// 注册新账户操作
		/// </summary>
		/// <param name="UserName">用户名</param>
		/// <param name="PassWord">密码</param>
		/// <returns></returns>
		public AssignResult DBSignIn(string UserName, string PassWord){
			UserName = UserName.Trim();
			if(UserName == "")
				return new AssignResult(AssignState.NAME_EMPTY);
			if(UserName.Length > LengthLimit.maxNameLength)
				return new AssignResult(AssignState.NAME_TOO_LONG);
			if(PassWord.Length > LengthLimit.maxPwdLength)
				return new AssignResult(AssignState.PWD_TOO_LONG);
			for(int i = 0; i < UserName.Length ;i++)
				if (UserName[i] <= ' ' || UserName[i] > 127)
					return new AssignResult(AssignState.NAME_ERROR_CHAR);
			for (int i = 0; i < PassWord.Length; i++)
				if (PassWord[i] < ' ')
					return new AssignResult(AssignState.PWD_ERROR_CHAR);
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select [name] from [dbo].[User] where [name] = '" + UserName + "'");
				if(reader.Read())
					return new AssignResult(AssignState.NAME_USED);
				reader.Close();
				reader = SqlOption(objConnection,
					"INSERT INTO [dbo].[User] ([name] , [password]) VALUES ('" + UserName + "','" + PassWord + "')");
				reader.Close();
				reader = SqlOption(objConnection,
					"select [id] from [dbo].[User] where [name] = '" + UserName + "'");
				reader.Read();
				return new AssignResult((int)reader[0]);
			}
			catch{
				return new AssignResult(AssignState.ERROR_WHILE_READ_DB);
			}
			finally{
				if(reader != null)
					reader.Close();
				if(objConnection != null)
					objConnection.Close();
			}
		}

        public LoginState LogIn(string UserName, string PassWord, string ClientIPAddress, DateTime time)
        {//登录并返回账号UserId
            try
            {
                SqlConnection Connection = new SqlConnection(strConnection);
                Connection.Open();
                SqlCommand cmd = new SqlCommand("select id,password from [SmallBones].dbo.[User] where name = '" + UserName + "'", Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                LoginState Result = LoginState.USERNOTEXIST;
                int id = 0;
                if (reader.Read())
                {
                    if (PassWord == (string)reader[1])
                    {
                        Result = LoginState.LOGIN;
                        id = (int)reader[0];
                    }
                    else
                        Result = LoginState.WRONGPASSWORD;
                }
                reader.Close();

                if (Result == LoginState.LOGIN)
                {
                    SqlCommand Log = new SqlCommand("insert into [SmallBones].dbo.[Login] (userid,inout,[time],ip) values (" + id.ToString() + ",1,'" + time.ToString() + "','" + ClientIPAddress + "')", Connection);
                    Log.ExecuteNonQuery();
                }
                Connection.Close();
                return Result;
            }
            catch
            {
                return LoginState.DBERROR;
            }
        }

		/// <summary>
		/// 获取编号在一个区间内的Group
		/// </summary>
		/// <param name="StartPosition">区间下界</param>
		/// <param name="EndPosition">区间上界，注意是左闭右开区间</param>
		/// <param name="sortForm">排序依据</param>
		/// <returns>若返回值的err成员为OK，则其他成员一定会被赋值，其他情况请参照DBError，未说明则不会赋值</returns>
		public GroupSet GetGroups(int StartPosition, int EndPosition, SortForm sortForm = SortForm.DEFAULT){//进入Groups界面，得到从StartPosition到EndPosition部分的Groups列表
			GroupSet groupSet = new GroupSet();
			if(StartPosition > EndPosition){
				groupSet.err = DBError.LOWER_BOUND_LARGER_THAN_HIGHER_BOUND;
				return groupSet;
			}
			if(StartPosition + LengthLimit.maxCountPerQuery < EndPosition){
				groupSet.err = DBError.SCOPE_TOO_LARGE;
				EndPosition = StartPosition + LengthLimit.maxCountPerQuery;
			}
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select max([id]) from [dbo].[Group]");
				if(reader.Read())
					groupSet.groupCount = (int)reader[0];
				else
					groupSet.groupCount = 0;
				reader.Close();
				if(sortForm == SortForm.LAST){
					int tmp = EndPosition;
					EndPosition = groupSet.groupCount - StartPosition + 2;
					StartPosition = groupSet.groupCount - tmp + 2;
				}
				reader = SqlOption(objConnection,
					"select * from [dbo].[Group] where [id] between " + StartPosition + " and " + (EndPosition - 1));
				List<IceBird.Group> groups = new List<IceBird.Group>();
				while(reader.Read())
					groups.Add((new Group(reader)));
				groupSet.Groups = groups;
				return groupSet;
			}
			catch
			{
				groupSet.err = DBError.DB_OPTION_ERROR;
				return groupSet;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 获取一个Group下，在该Group下编号在一个区间内的Topic
		/// </summary>
		/// <param name="StartPosition">区间下界</param>
		/// <param name="EndPosition">区间上界，注意是左闭右开区间</</param>
		/// <param name="GroupId">群组编号</param>
		/// <param name="sortForm">排序依据</param>
		/// <returns>若返回值的err成员为OK，则其他成员一定会被赋值，其他情况请参照DBError，未说明则不会赋值</returns>
		public TopicSet GetTopics(int StartPosition, int EndPosition, int GroupId, SortForm sortForm = SortForm.DEFAULT){//进入Topics界面，得到从SP到EP部分的Topics列表
			TopicSet topicSet = new TopicSet();
			if(StartPosition > EndPosition){
				topicSet.err = DBError.LOWER_BOUND_LARGER_THAN_HIGHER_BOUND;
				return topicSet;
			}
			if (StartPosition + LengthLimit.maxCountPerQuery < EndPosition)
			{
				topicSet.err = DBError.SCOPE_TOO_LARGE;
				EndPosition = StartPosition + LengthLimit.maxCountPerQuery;
			}
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[Group] where [id] = " + GroupId);
				if(!reader.Read()){
					topicSet.err = DBError.NO_SUCH_ID;
					return topicSet;
				}
				topicSet.group = new Group(reader);
				reader.Close();
				if (sortForm == SortForm.LAST)
				{
					int tmp = EndPosition;
					EndPosition = topicSet.group.topicCnt - StartPosition + 2;
					StartPosition = topicSet.group.topicCnt - tmp + 2;
				}
				reader = SqlOption(objConnection,
					"select * from [dbo].[Topic] where ([groupid] = " + GroupId + " ) and ([idingroup] between " + StartPosition + "and " + (EndPosition - 1) + ")");
				List<IceBird.Topic> topics = new List<IceBird.Topic>();
				while(reader.Read())
					topics.Add(new Topic(reader));
				topicSet.Topics = topics;
				return topicSet;
			}
			catch
			{
				topicSet.err = DBError.DB_OPTION_ERROR;
				return topicSet;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 获取一个Topic下，在该Topic下编号在一个区间内的Message
		/// </summary>
		/// <param name="StartPosition">区间下界</param>
		/// <param name="EndPosition">区间上界，注意是左闭右开区间</param>
		/// <param name="TopicId">Topic编号</param>
		/// <param name="sortForm">排序依据</param>
		/// <returns>若返回值的err成员为OK，则其他成员一定会被赋值，其他情况请参照DBError，未说明则不会赋值</returns>
		public MessageSet GetReplies(int StartPosition, int EndPosition, int TopicId, SortForm sortForm = SortForm.DEFAULT){
			MessageSet messageSet = new MessageSet();
			if (StartPosition > EndPosition){
				messageSet.err = DBError.LOWER_BOUND_LARGER_THAN_HIGHER_BOUND;
				return messageSet;
			}
			if (StartPosition + LengthLimit.maxCountPerQuery < EndPosition)
			{
				messageSet.err = DBError.SCOPE_TOO_LARGE;
				EndPosition = StartPosition + LengthLimit.maxCountPerQuery;
			}
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[Topic] where [id] = " + TopicId);
				if(!reader.Read()){
					messageSet.err = DBError.NO_SUCH_ID;
					return messageSet;
				}
				messageSet.topic = new Topic(reader);
				reader.Close();
				if (sortForm == SortForm.LAST)
				{
					int tmp = EndPosition;
					EndPosition = messageSet.topic.replyCnt - StartPosition + 2;
					StartPosition = messageSet.topic.replyCnt - tmp + 2;
				}
				reader = SqlOption(objConnection,
					"select * from [dbo].[Reply] where ([topicid] = " + TopicId + " ) and ([idintopic] between " + StartPosition + " and " + (EndPosition - 1) + ")");
				List<IceBird.Message> Replies = new List<IceBird.Message>();
				while (reader.Read())
					Replies.Add(new Message(reader));
				messageSet.Messages = Replies;
				return messageSet;
			}
			catch
			{
				messageSet.err = DBError.DB_OPTION_ERROR;
				return messageSet;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}
		
		/// <summary>
		/// 新建一个Message
		/// </summary>
		/// <param name="UserId">用户id，用户id不存在createState会被赋值为ERROR_WHILE_READ_DB</param>
		/// <param name="Content">回复的文本</param>
		/// <param name="TopicId">回复的Topic的绝对编号（即Topic类下的id而非idInGroup和NewTopic的返回值）</param>
		/// <returns>若createState成员为Pass，则id为传入的Topic下该Message的编号，否则id为空</returns>
		public CreateResult CommitReply(int UserId, string Content, int TopicId){
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			if(Content.Length > LengthLimit.maxMessageLength)
				return new CreateResult(CreateState.TOO_LONG);
			try{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[topic] where([id] = " + TopicId + ")");
				if(!reader.Read())
					return new CreateResult(CreateState.NO_SUCH_ID);
				Topic topic = new Topic(reader);
				reader.Close();
				reader = SqlOption(objConnection,
					"update [dbo].[topic] set [replycnt] = " + (topic.replyCnt + 1) + " where [id] = " + topic.id);
				reader.Close();
				reader = SqlOption(objConnection,
					"insert [dbo].[reply] ([userid],[time],[topicid],[content],[idintopic]) values " +
					"(" + UserId + ", '" + DateTime.Now.ToString() + "', " + TopicId + ", '" + Content + "', " + (topic.replyCnt + 1) + ")");
				reader.Close();
				return new CreateResult(topic.replyCnt + 1);
			}
			catch
			{
				return new CreateResult(CreateState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 新建一个Topic
		/// </summary>
		/// <param name="UserId">用户id，用户id不存在createState会被赋值为ERROR_WHILE_READ_DB</param>
		/// <param name="Content">Topic的名称</param>
		/// <param name="GroupId">所在的Group的编号</param>
		/// <returns>若createState成员为Pass，则id为传入的Group下该Topic的编号，否则id为空</returns>
		public CreateResult NewTopic(int UserId, string TopicName, int GroupId)
		{
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			if (TopicName.Length > LengthLimit.maxTopicLength)
				return new CreateResult(CreateState.TOO_LONG);
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[group] where [id] = " + GroupId);
				if (!reader.Read())
					return new CreateResult(CreateState.NO_SUCH_ID);
				Group group = new Group(reader);
				reader.Close();
				reader = SqlOption(objConnection,
					"update [dbo].[group] set [topiccnt] = " + (group.topicCnt + 1) + " where [id] = " + group.id);
				reader.Close();
				reader = SqlOption(objConnection,
					"insert [dbo].[topic] ([userid],[time],[groupid],[title],[idingroup]) values " +
					"(" + UserId + ", '" + DateTime.Now.ToString() + "', " + GroupId + ", '" + TopicName + "', " + (group.topicCnt + 1) + ")");
				reader.Close();
				return new CreateResult(group.topicCnt + 1);
			}
			catch
			{
				return new CreateResult(CreateState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 新建一个Group
		/// </summary>
		/// <param name="GroupName">Group的名称</param>
		/// <returns>若createState成员为Pass，则id为该Group的编号，否则id为空</returns>
		public CreateResult NewGroup(string GroupName)
		{
			if (GroupName.Length > LengthLimit.maxGroupLength)
				return new CreateResult(CreateState.TOO_LONG);
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"insert [dbo].[group] ([name]) values " + "('" + GroupName + "')");
				reader.Close();
				reader = SqlOption(objConnection,
					"select [id] from [dbo].[group] where [name] = '" + GroupName + "'");
				reader.Read();
				int id = (int)reader[0];
				reader.Close();
				return new CreateResult(id);
			}
			catch
			{
				return new CreateResult(CreateState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		//recommend:add LoginTime as a parameter,
		//recommend:return int to indicate the signin status,
        public bool LogOut(string UserName, string ClientIPAddress, DateTime time){//用户登出记录
            try
            {
                SqlConnection Connection = new SqlConnection(strConnection);
                Connection.Open();
                SqlCommand cmd = new SqlCommand("select id from [SmallBones].dbo.[User] where name ='"+UserName+"'", Connection);
                SqlDataReader reader = cmd.ExecuteReader();
                int id = 0;
                if (reader.Read())
                {
                    id = (int)reader[0]; 
                }
                reader.Close();

                if (id != 0)
                {
                    SqlCommand Log = new SqlCommand("insert into [SmallBones].dbo.[Login] (userid,inout,[time],ip) values (" + id.ToString() + ",0,'" + time.ToString() + "','" + ClientIPAddress + "')", Connection);
                    Log.ExecuteNonQuery();
                    Connection.Close();
                    return true;
                }
                Connection.Close();
                return false;
            }
            catch
            {
                return false;
            }
		}
		//recommend:remove ipaddr from parameter,*/
	}

}